﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MediaLibraryManagement.Entity;

namespace MediaLibraryManagement.DAL
{
    public class CastDAL : BaseDAL
    {
        #region Properties Implementation

        protected override string TableName { get { return DatabaseTableName.Casts; } }

        #endregion

        public List<Cast> GetByMovieID(int movieID)
        {
            List<Cast> casts = new List<Cast>();
            try
            {
                OpenConnection();
                string commandText = string.Format("SELECT * FROM {0} WHERE MovieID = {1}", TableName, movieID);
                using (SqlCommand command = new SqlCommand(commandText, Connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Cast cast = (Cast)Fetch(reader);
                            casts.Add(cast);
                        }
                        reader.Close();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
            return casts;
        }

        public void Insert(List<Cast> casts)
        {
            try
            {
                OpenConnection();
                string commandstring = string.Format("INSERT INTO {0} (MovieID, Artist, Role) "
                                                            + "VALUES (@MovieID, @Artist, @Role)"
                                                    , TableName);
                using (SqlCommand command = new SqlCommand(commandstring, Connection))
                {
                    command.Parameters.Add("@MovieID", SqlDbType.Int);
                    command.Parameters.Add("@Artist", SqlDbType.NVarChar);
                    command.Parameters.Add("@Role", SqlDbType.NVarChar);
                    foreach (Cast cast in casts)
                    {
                        command.Parameters["@MovieID"].Value = cast.MovieID;
                        command.Parameters["@Artist"].Value = cast.Artist;
                        command.Parameters["@Role"].Value = cast.Role;
                        command.ExecuteNonQuery();
                    }
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        public void DeleteByMovieID(int movieID)
        {
            try
            {
                OpenConnection();
                string commandText = string.Format("DELETE FROM {0} WHERE MovieID = {1}", TableName, movieID);
                using (SqlCommand command = new SqlCommand(commandText, Connection))
                {
                    command.ExecuteNonQuery();
                }
                CloseConnection();
            }
            catch (Exception ex)
            {
                CloseConnection();
                throw ex;
            }
        }

        #region Private Methods

        protected override IEntity Fetch(IDataReader reader)
        {
            Cast cast = new Cast();
            cast.ID = (Int32)reader["ID"];
            cast.MovieID = (Int32)reader["MovieID"];
            cast.Artist = reader["Artist"].ToString();
            cast.Role = reader["Role"].ToString();
            return cast;
        }

        #endregion
    }
}